package com.spreadk.demo;

import com.spreadk.demo.entity.Student;

import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * @author:Gan Linghao
 * @date:2021/4/18
 * @time:15:17
 * @description: jdbc工具类
 */
public class JDBCUtil {

    // 驱动
//    private static String driver = "com.mysql.cj.jdbc.Driver";
//    // mysql数据库地址及链接的数据库
//    private static String url = "jdbc:mysql://115.159.223.173:3366/test";
//    //用户名
//    private static String username = "root";
//    // 密码
//    private static String pwd = "glh17730269920";
    // 驱动
    private static String driver;
    // mysql数据库地址及链接的数据库
    private static String url;
    //用户名
    private static String username;
    // 密码
    private static String pwd;

    static {
        //法一
//        ClassLoader cls = JDBCUtil.class.getClassLoader();
//        URL res = cls.getResource("jdbc.properties");
        //法二
        InputStream res = ClassLoader.getSystemResourceAsStream("jdbc.properties");


        Properties pro = new Properties();
        try {
//            pro.load(new FileReader(res.getPath()));
            pro.load(res);

            driver = (String) pro.get("driver");
            url = (String) pro.get("url");
            username = (String) pro.get("username");
            pwd = (String) pro.get("pwd");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 获取连接
     * @return
     */
    public static Connection getConnection(){
        Connection connection = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, username, pwd);
            System.out.println(connection);

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 释放资源
     * @param connection
     * @param ps
     */
    public static void close(Connection connection,PreparedStatement ps){

            try {
                if (connection != null) {
                    connection.close();
                }
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
    }

    /**
     * 查询操作
     * @param sql 待执行的查询语句
     * @return
     */
    public static List<Student> select(String sql){

        ArrayList<Student> list = new ArrayList<>();

        Connection connection = getConnection();

        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            System.out.println(rs);
            while (rs.next()) {
                Student student = new Student();
                Integer id = rs.getInt("sid");
                String sname = rs.getString("sname");
                String gender = rs.getString("gender");
                Integer classId = rs.getInt("class_id");

                student.setSid(id);
                student.setSname(sname);
                student.setGender(gender);
                student.setClassId(classId);
//                System.out.println(id + " -- " + sname + " -- " + gender + " -- " + classId);

                list.add(student);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //释放资源
            close(connection,ps);
        }
        return list;
    }

    /**
     * 插入
     * @param sql 插入用的sql
     * @return
     */
    public static Integer insert(String sql){
        Connection connection = getConnection();

        PreparedStatement ps = null;
        Integer rows = 0;
        try {
            ps = connection.prepareStatement(sql);
            rows = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //释放资源
            close(connection,ps);
        }
        return rows;
    }

    public static Boolean delete(String sql){
        Connection connection = getConnection();

        PreparedStatement ps = null;
        Boolean rows = true;
        try {
            ps = connection.prepareStatement(sql);
            rows = ps.execute(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //释放资源
            close(connection,ps);
        }
        return rows;
    }

    public static void main(String[] args) {

        String sql1 = "select * from student";
        List<Student> studentList = select(sql1);
        System.out.println(studentList);

//        String sql2 = "insert into student ('sname', 'gender', 'class_id') values ('王五000','男', 1)";
//        Student stu = new Student();
//        stu.setSname("里斯1888899999");
//        stu.setGender("男");
//        stu.setClassId(2);
//
//        String sql2 = "insert into student (sname, gender, class_id) values ('"
//                + stu.getSname()
//                +"','" +stu.getGender()
//                +"'," + stu.getClassId() +")";
//        System.out.println(sql2);
//        Integer rows = insert(sql2);
//        System.out.println("成功影响的行数：" + rows);
//
//        String name = "测试姓名里斯";
//        Integer sid = 4;
//
//        String sql3 = "update student set sname = '" +name + "' where sid = " + sid;
//        rows = insert(sql3);
//        System.out.println("成功影响的行数：" + rows);

//        Integer sid = 27;
//        String sql4 = "delete from student where sid = " + sid;
//        Boolean flag = delete(sql4);
//        System.out.println("删除成功：" + flag);
    }

}
